package com.etone.smartAudit.utils.database;

import com.etone.daemon.db.helper.QueryHelper;
import com.etone.smartAudit.domain.DataMeta;
import com.etone.smartAudit.enums.DataBaseType;
import com.etone.smartAudit.error.CustomParameterizedException;
import lombok.extern.log4j.Log4j2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author: sovy
 * @date: 2020/11/16
 * @description:
 */
@Log4j2
public class DatabaseUtil {

    protected static final String SPACE = " ";

    /**
     * 创建表
     *
     * @param ip
     * @param userName
     * @param password
     * @param port
     * @param database
     * @param schema
     * @param tableName
     * @param dataMetas
     * @param dataBaseType
     */
    public static void createTable(String ip, String userName, String password, Integer port, String database, String schema, String tableName, List<DataMeta> dataMetas, DataBaseType dataBaseType) {
        switch (dataBaseType) {
            case GREENPLUM:
                GreenplumUtil.createTable(ip, userName, password, port, database, schema, tableName, dataMetas);
                break;
            case MYSQL:
                MysqlUtil.createTable(ip, userName, password, port, database, tableName, dataMetas);
                break;
            default:
        }
    }

    /**
     * 删除表
     *
     * @param ip
     * @param userName
     * @param password
     * @param port
     * @param database
     * @param schema
     * @param tableName
     * @param dataBaseType
     */
    public static void deleteTable(String ip, String userName, String password, Integer port, String database, String schema, String tableName, DataBaseType dataBaseType) {
        switch (dataBaseType) {
            case GREENPLUM:
                GreenplumUtil.deleteTable(ip, userName, password, port, database, schema, tableName);
                break;
            case MYSQL:
                MysqlUtil.deleteTable(ip, userName, password, port, database, tableName);
                break;
            default:
        }
    }

    /**
     * 授权查询
     *
     * @param ip
     * @param userName
     * @param password
     * @param port
     * @param database
     * @param tableName
     * @param toUserName
     */
    public static void authTableSelect(String ip, String userName, String password, Integer port, String database, String schema, String tableName, String toUserName, DataBaseType dataBaseType) {
        switch (dataBaseType) {
            case GREENPLUM:
                GreenplumUtil.authTableSelect(ip, userName, password, port, database, schema, tableName, toUserName);
                break;
            case MYSQL:
                MysqlUtil.authTableSelect(ip, userName, password, port, database, tableName, toUserName);
                break;
            default:
        }
    }

    /**
     * 执行sql语句
     *
     * @param ip           ip
     * @param user         用户名
     * @param password     密码
     * @param port         端口
     * @param database     数据库名称
     * @param sqls         执行语句
     * @param dataBaseType 数据库类型
     * @return
     */
    public static Boolean execSql(String ip, String user, String password, Integer port, String database, List<String> sqls, DataBaseType dataBaseType) {
        Connection con = null;
        try {
            con = getConnection(ip, dataBaseType, user, password, port, database);
            con.setAutoCommit(false);
            for (String sql : sqls) {
                log.info("exec sql ----> {}", sql);
                PreparedStatement preparedStatement = con.prepareStatement(sql);
                preparedStatement.execute();
                preparedStatement.close();
            }
            con.commit();
        } catch (Exception e) {
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
            throw new CustomParameterizedException(e.getMessage());
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * select 查询
     *
     * @param ip
     * @param user
     * @param password
     * @param port
     * @param database
     * @param sql
     * @param dataBaseType
     * @return
     */
    public static List<Map<String, Object>> selectSql(String ip, String user, String password, Integer port, String database, String sql, DataBaseType dataBaseType) {

        try {
            Connection connection = getConnection(ip, dataBaseType, user, password, port, database);
            log.info("查询sql:{}", sql);
            List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
            return maps;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取 Connection
     *
     * @param ip           ip
     * @param dataBaseType 数据库类型
     * @param user         用户名
     * @param password     密码
     * @param port         端口
     * @param database     数据库
     * @return
     */
    public static Connection getConnection(String ip, DataBaseType dataBaseType, String user, String password, int port, String database) throws SQLException, ClassNotFoundException {
        log.info("jdbc连接信息---->ip:{},数据库类型:{},用户名:{},密码:{},端口:{},数据库:{}", ip, dataBaseType, user, password, port, database);
        Connection con = null;
        switch (dataBaseType) {
            case GREENPLUM:
                Class.forName("org.postgresql.Driver");
                con = DriverManager
                        .getConnection("jdbc:postgresql://" + ip + ":" + port + "/" + database,
                                user, password);
                break;
            case MYSQL:
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager
                        .getConnection("jdbc:mysql://" + ip + ":" + port + "/" + database,
                                user, password);
                break;
            default:
        }

        return con;
    }

    /**
     * 组装空格分隔符
     *
     * @param strs
     * @return
     */
    public static String splitSpace(String... strs) {
        String result = "";
        for (String str : strs) {
            result += str + DatabaseUtil.SPACE;
        }
        return result;
    }

}
